﻿using INTEX.DataBase;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using System.Linq;
using System.Windows;
namespace WPFExcelReport
{
    public partial class ITXEXRPT
    {
        public void FLTABMCO(DataSet db) 
        {
            int xStart = 4;
            string LO = "";
            DataTable dtOutput = new DataTable();
            try
            {
                #region======Title===========
                exAgent.SetCellValue(3, 1, "ROWNID"); dtOutput.Columns.Add("ROWNID", typeof(System.String));
                exAgent.SetCellValue(3, 2, "LO");   dtOutput.Columns.Add("LO", typeof(System.String));
                exAgent.SetCellValue(3, 3, "MARTDES"); dtOutput.Columns.Add("MARTDES", typeof(System.String));
                exAgent.SetCellValue(3, 4, "MART"); dtOutput.Columns.Add("MART", typeof(System.String));
                exAgent.SetCellValue(3, 5, "FARTDES");dtOutput.Columns.Add("FARTDES", typeof(System.String));
                exAgent.SetCellValue(3, 6, "MARTMOC"); dtOutput.Columns.Add("MARTMOC", typeof(System.String));
                exAgent.SetCellValue(3, 7, "TR00"); dtOutput.Columns.Add("TR00", typeof(System.String));
                exAgent.SetCellValue(3, 8, "TR00AUF"); dtOutput.Columns.Add("TR00AUF", typeof(System.String));
                exAgent.SetCellValue(3, 9, "VTR00"); dtOutput.Columns.Add("VTR00", typeof(System.String));
                exAgent.SetCellValue(3, 10, "TR10"); dtOutput.Columns.Add("TR10", typeof(System.String));
                exAgent.SetCellValue(3, 11, "VTR10"); dtOutput.Columns.Add("VTR10", typeof(System.String));
                exAgent.SetCellValue(3, 12, "TR11"); dtOutput.Columns.Add("TR11", typeof(System.String));
                exAgent.SetCellValue(3, 13, "DSTOCK"); dtOutput.Columns.Add("DSTOCK", typeof(System.String));
                exAgent.SetCellValue(3, 14, "DPRICE"); dtOutput.Columns.Add("DPRICE", typeof(System.String));
                exAgent.SetCellValue(3, 15, "AB13"); dtOutput.Columns.Add("AB13", typeof(System.String));
                exAgent.SetCellValue(3, 16, "WASTE"); dtOutput.Columns.Add("WASTE", typeof(System.String));
                exAgent.SetCellValue(3, 17, "AB36"); dtOutput.Columns.Add("AB36", typeof(System.String));
                exAgent.SetCellValue(3, 18, "STOCKCOST"); dtOutput.Columns.Add("STOCKCOST", typeof(System.String));
                exAgent.SetCellValue(3, 19, "VSTOCKCOST"); dtOutput.Columns.Add("VSTOCKCOST", typeof(System.String));
                exAgent.SetCellValue(3, 20, "TR99AUF"); dtOutput.Columns.Add("TR99AUF", typeof(System.String));
                exAgent.SetCellValue(3, 21, "TR99"); dtOutput.Columns.Add("TR99", typeof(System.String));
                exAgent.SetCellValue(3, 22, "VTR99"); dtOutput.Columns.Add("VTR99", typeof(System.String));
                #endregion 
                #region======ConvertData===

                var qGW = from GW in db.Tables["ICC_EVLCOST"].AsEnumerable()
                          group GW by new
                          {
                              LO = GW["LO"].ToString(),
                          }
                              into g
                              select new
                              {
                                  KEY = g.Key,
                              };
                foreach (var GW in qGW)
                {
                    LO = GW.KEY.LO;
                    exAgent.SetCurrentSheet("FLTABMCO");
                    exAgent.CopySheet(LO);
                    DataRow[] rows = db.Tables["ICC_EVLCOST"].Select("LO='" + LO + "'");
                    DataTable dtSoruce = rows.CopyToDataTable();
                    dtOutput.Clear();
                    dtOutput.Merge(dtSoruce, false, MissingSchemaAction.Ignore);
                    foreach (DataRow rx in dtOutput.Rows)
                    {
                        rx["TR00AUF"] = System.Math.Round(Convert.ToDecimal((rx["TR00AUF"].ToString())) / 1000, 2);
                        rx["TR00"] = System.Math.Round(Convert.ToDecimal((rx["TR00"].ToString())) / 1000, 2);
                        rx["VTR00"] = System.Math.Round(Convert.ToDecimal((rx["VTR00"].ToString())) / 1000, 2);
                        rx["TR10"] = System.Math.Round(Convert.ToDecimal((rx["TR10"].ToString())) / 1000, 2);
                        rx["VTR10"] = System.Math.Round(Convert.ToDecimal((rx["VTR10"].ToString())) / 1000, 2);
                        rx["TR11"] = System.Math.Round(Convert.ToDecimal((rx["TR11"].ToString())) / 1000, 2);
                        rx["DPRICE"] = System.Math.Round(Convert.ToDecimal((rx["DPRICE"].ToString())) / 1000, 2);
                        rx["AB13"] = System.Math.Round(Convert.ToDecimal((rx["AB13"].ToString())) / 1000, 2);
                        rx["AB36"] = System.Math.Round(Convert.ToDecimal((rx["AB36"].ToString())) / 1000, 2);
                        rx["DSTOCK"] = System.Math.Round(Convert.ToDecimal((rx["DSTOCK"].ToString())) / 1000, 2);
                        rx["WASTE"] = System.Math.Round(Convert.ToDecimal((rx["WASTE"].ToString())) / 1000, 2);
                        rx["STOCKCOST"] = System.Math.Round(Convert.ToDecimal((rx["STOCKCOST"].ToString())) / 1000, 2);
                        rx["VSTOCKCOST"] = System.Math.Round(Convert.ToDecimal((rx["VSTOCKCOST"].ToString())) / 1000, 2);
                        rx["TR99AUF"] = System.Math.Round(Convert.ToDecimal((rx["TR99AUF"].ToString())) / 1000, 2); 
                        rx["TR99"] = System.Math.Round(Convert.ToDecimal((rx["TR99"].ToString())) / 1000, 2);
                        rx["VTR99"] = System.Math.Round(Convert.ToDecimal((rx["VTR99"].ToString())) / 1000, 2);
                    }
                #endregion

                    #region====Excel==========
                    exAgent.DataTableToExcel(dtOutput, xStart, 1);
                    int count = dtOutput.Rows.Count;
                    exAgent.SetCellValue(1, 1, 1);
                    exAgent.BatchConvert2Number(4, 7, count + 3, 20);
                    MergeSameFART(count);
                    #endregion
                }
                exAgent.SetCurrentSheet("FLTABMCO");
                dtOutput.Clear();
                dtOutput.Merge(db.Tables["ICC_EVLCOST"], false, MissingSchemaAction.Ignore); ;
                foreach (DataRow rx in dtOutput.Rows)
                {
                    rx["TR00AUF"] = System.Math.Round(Convert.ToDecimal((rx["TR00AUF"].ToString())) / 1000, 2);
                    rx["TR00"] = System.Math.Round(Convert.ToDecimal((rx["TR00"].ToString())) / 1000, 2);
                    rx["VTR00"] = System.Math.Round(Convert.ToDecimal((rx["VTR00"].ToString())) / 1000, 2);
                    rx["TR10"] = System.Math.Round(Convert.ToDecimal((rx["TR10"].ToString())) / 1000, 2);
                    rx["VTR10"] = System.Math.Round(Convert.ToDecimal((rx["VTR10"].ToString())) / 1000, 2);
                    rx["TR11"] = System.Math.Round(Convert.ToDecimal((rx["TR11"].ToString())) / 1000, 2);
                    rx["DPRICE"] = System.Math.Round(Convert.ToDecimal((rx["DPRICE"].ToString())) / 1000, 2);
                    rx["AB13"] = System.Math.Round(Convert.ToDecimal((rx["AB13"].ToString())) / 1000, 2);
                    rx["AB36"] = System.Math.Round(Convert.ToDecimal((rx["AB36"].ToString())) / 1000, 2);
                    rx["DSTOCK"] = System.Math.Round(Convert.ToDecimal((rx["DSTOCK"].ToString())) / 1000, 2);
                    rx["WASTE"] = System.Math.Round(Convert.ToDecimal((rx["WASTE"].ToString())) / 1000, 2);
                    rx["STOCKCOST"] = System.Math.Round(Convert.ToDecimal((rx["STOCKCOST"].ToString())) / 1000, 2);
                    rx["VSTOCKCOST"] = System.Math.Round(Convert.ToDecimal((rx["VSTOCKCOST"].ToString())) / 1000, 2);
                    rx["TR99AUF"] = System.Math.Round(Convert.ToDecimal((rx["TR99AUF"].ToString())) / 1000, 2);
                    rx["TR99"] = System.Math.Round(Convert.ToDecimal((rx["TR99"].ToString())) / 1000, 2);
                    rx["VTR99"] = System.Math.Round(Convert.ToDecimal((rx["VTR99"].ToString())) / 1000, 2);
                }
                exAgent.DataTableToExcel(dtOutput, xStart, 1);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StopProgress);
            }
        }
        public void MergeSameFART(int rows)
        {
            int x1, y1, x2, y2;
            string str = "";
            string str1;
            y1 = 5;
            y2 = 5;
            x1 = 4;
            x2 = 4;
            for (int i = 4; i <= rows + 4; i++)
            {
                str1 = (string)exAgent.GetCellValue(i, 5);
                if (str == str1)
                {
                    x2 = i;
                }
                else
                {
                    if (x2 > x1)
                    {
                        exAgent.Merge(x1, y1, x2, y2);
                        exAgent.Merge(x1, 14, x2, 14);
                    }
                    x1 = i;
                    str = str1;
                }
            }
        }
    }
}